package com.zerowidth.db.op;

import com.zerowidth.db.DBConn;
import com.zerowidth.db.DBHelper;
import com.zerowidth.db.base.SqlResult;
import com.zerowidth.db.bean.UserBean;
import com.zerowidth.db.utils.IOUtils;
import com.zerowidth.db.utils.StringUtils;
import com.zerowidth.db.utils.TimeUtils;
import com.zerowidth.moc.web.utils.MathUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDB {

    public static int count(String start_time, String end_time) {
        String sql = String.format("SELECT COUNT(*) FROM album_user WHERE create_time >= '%s' " +
                " AND create_time <= '%s'", start_time, end_time);

        return DBHelper.queryCount(sql);
    }

    public static boolean updateUpdateTimeByUserId(String userId) {
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("UPDATE album_user SET update_time = '%s', use_count = use_count + 1 " +
                " WHERE user_id = '%s'", time, userId);

        return DBHelper.executeUpdate(sql) > 0;
    }

    public static UserBean findUserByPhone(String phoneNumber) {
        if (StringUtils.isEmpty(phoneNumber)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where phone_number = '%s'", phoneNumber);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByStatId(String statid) {
        if (StringUtils.isEmpty(statid)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where stat_id = '%s'", statid);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByUserId(String userId) {
        if (StringUtils.isEmpty(userId)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where user_id = '%s'", userId);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.dingding_pkg_version = rs.getInt("dingding_pkg_version");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");


                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByUserIdLike(String userId8) {
        if (StringUtils.isEmpty(userId8)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user WHERE user_id LIKE '%s%%'", userId8);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");


                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByWxNameLike(String wxName) {
        if (StringUtils.isEmpty(wxName)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user WHERE wx_nickname LIKE '%s%%'", wxName);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");


                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static int countUserByUserIdLike(String userId8) {
        if (StringUtils.isEmpty(userId8)) {
            return 0;
        }
        String sql = String.format("SELECT COUNT(*) FROM album_user WHERE user_id LIKE '%s%%'", userId8);
        return DBHelper.queryCount(sql);
    }

    public static UserBean findUserByKey(String key) {
        if (StringUtils.isEmpty(key)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user WHERE nick_name LIKE '%%%s%%'" +
                " OR phone_number LIKE '%%%s%%'" +
                " OR user_id LIKE '%%%s%%'" +
                " ORDER BY update_time DESC", key, key, key);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;

            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.stat_id = rs.getString("stat_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByWXUnionid(String wxUnionid) {
        if (StringUtils.isEmpty(wxUnionid)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where wx_unionid = '%s'", wxUnionid);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByQQOpenid(String qqOpenId) {
        if (StringUtils.isEmpty(qqOpenId)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where qq_open_id = '%s'", qqOpenId);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }
            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static UserBean findUserByQQOpenidAndStatId(String statId) {
        if (StringUtils.isEmpty(statId)) {
            return null;
        }
        String sql = String.format("SELECT * FROM album_user where " +
                " qq_open_id_bind_stat_id = '%s'", statId);
        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                UserBean userBean = new UserBean();
                userBean.user_id = rs.getString("user_id");
                userBean.phone_number = rs.getString("phone_number");
                userBean.nick_name = rs.getString("nick_name");
                userBean.gender = rs.getInt("gender");
                userBean.birthday = rs.getString("birthday");
                userBean.avatar = rs.getString("avatar");
                userBean.vip_type = rs.getInt("vip_type");
                userBean.vip_start_time = rs.getString("vip_start_time");
                userBean.vip_end_time = rs.getString("vip_end_time");

                return userBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }

    public static int createUserByPhone(UserBean userBean) {
        if (userBean == null) {
            return -1;
        }
        String currentTime = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO album_user (user_id, phone_number, create_time, update_time) " +
                        " VALUES ('%s', '%s', '%s', '%s')",
                userBean.user_id, userBean.phone_number, currentTime, currentTime);
        return DBHelper.executeInsert(sql);
    }

    public static int createUserByRegister(UserBean userBean) {
        if (userBean == null) {
            return -1;
        }
        String currentTime = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO album_user (user_id, phone_number, pwd_md5, stat_id, create_time, update_time) " +
                        " VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
                userBean.user_id, userBean.phone_number, userBean.pwd, userBean.stat_id, currentTime, currentTime);
        return DBHelper.executeInsert(sql);
    }

    public static int createUserByWX(UserBean userBean, String wx_unionid, String wx_nickname, String wx_headimgurl) {
        if (userBean == null) {
            return -1;
        }
        String currentTime = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO album_user (user_id, nick_name, avatar, wx_unionid, wx_nickname, " +
                        " wx_headimgurl, create_time, update_time) " +
                        " VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                userBean.user_id, userBean.nick_name, userBean.avatar, wx_unionid, wx_nickname, wx_headimgurl, currentTime, currentTime);
        return DBHelper.executeInsert(sql);
    }

    public static int createUserByQQ(UserBean userBean, String qqOpenId, String qq_nickname, String qq_headimgurl,
                                     String qq_open_id_bind_stat_id) {
        if (userBean == null) {
            return -1;
        }
        String currentTime = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String sql = String.format("INSERT INTO album_user (user_id, nick_name, avatar, qq_open_id, qq_nickname, " +
                        " qq_headimgurl, qq_open_id_bind_stat_id, create_time, update_time) " +
                        " VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                userBean.user_id, userBean.nick_name, userBean.avatar, qqOpenId,
                qq_nickname, qq_headimgurl, qq_open_id_bind_stat_id, currentTime, currentTime);
        return DBHelper.executeInsert(sql);
    }

    public static boolean updateVipType(String userId, int vipType, String vip_start_time, String vip_end_time) {
        String sql = String.format("UPDATE album_user SET vip_type=%d , vip_start_time = '%s', vip_end_time = '%s' " +
                "WHERE user_id='%s'", vipType, vip_start_time, vip_end_time, userId);

        return DBHelper.executeUpdate(sql) > 0;
    }

    public static boolean updateDingSafeVersion(String userId, int dingding_pkg_version) {
        String sql = String.format("UPDATE album_user SET dingding_pkg_version=%d " +
                "WHERE user_id='%s'", dingding_pkg_version, userId);

        return DBHelper.executeUpdate(sql) > 0;
    }

    public static boolean updateUserData(String userId, String avatar, String nick_name, String birthday, int gender) {

        List<Object> params = new ArrayList<>();
        if (StringUtils.isNotEmpty(avatar)) {
            params.add(avatar);
        }
        if (StringUtils.isNotEmpty(nick_name)) {
            params.add(nick_name);
        }
        if (StringUtils.isNotEmpty(birthday)) {
            params.add(birthday);
        }
        if (gender > 0) {
            params.add(gender);
        }
        params.add(userId); //最后用于忽略最后一项逗号的影响
        params.add(userId); //where的case

        Object[] objectArr = new Object[params.size()];
        for (int i = 0; i < params.size(); i++) {
            objectArr[i] = params.get(i);
        }

        String sql = String.format("UPDATE album_user SET " +
                ((StringUtils.isNotEmpty(avatar)) ? " avatar= '%s', " : "") +
                ((StringUtils.isNotEmpty(nick_name)) ? " nick_name = '%s', " : "") +
                ((StringUtils.isNotEmpty(birthday)) ? " birthday = '%s', " : "") +
                ((gender > 0) ? " gender = %d, " : "") +
                " user_id = '%s' " +
                "WHERE user_id='%s'", objectArr);

        return DBHelper.executeUpdate(sql) > 0;
    }

    public static boolean updateUserPwd(String userId, String pwd_key_md5) {
        String sql = String.format("UPDATE album_user SET " +
                "pwd_md5='%s' WHERE user_id='%s'", pwd_key_md5, userId);
        return DBHelper.executeUpdate(sql) > 0;
    }

    public static boolean checkPwdLogin(String phone, String pwdKeyMd5) {
        String sql = String.format("SELECT * FROM album_user " +
                        "WHERE phone_number = '%s' and pwd_md5 = '%s'",
                phone, pwdKeyMd5);

        ResultSet rs = null;
        try {
            rs = DBConn.getInst().getStmt().executeQuery(sql);

            rs.last();
            return rs.getRow() > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        return false;
    }

    public static String randomPickName() {
        int offset = MathUtil.random(1, 18000);

        String sql = String.format("SELECT nick_name FROM album_user WHERE nick_name IS NOT NULL " +
                " LIMIT 1 OFFSET %d", offset);

        SqlResult sr = DBHelper.executeQueryEx(sql);

        try {
            if (sr == null) {
                return null;
            }

            ResultSet rs = sr.rs;
            if (rs.next()) {
                return rs.getString("nick_name");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(sr);
        }

        return null;
    }


}
